import datetime
import os
import copy
import numpy as np
import pandas as pd
import Core.Gadget as Gadget
import Core.Config as Config
from SystematicFactors.General import Save_Systematic_Factor_To_Database
from IndustryRotation.industry_factor_utility import calc_total_fundamental_value_ttm
from IndustryRotation.industry_factor_utility import calc_total_fundamental_value_avg
from DataProcess.download_stock_list_jp import load_historical_nikkei225_content

# 全AROE
# 文件位置 total_a_roe.xlsx
# 程序位置 Calculate_Aggregate_ROE

# 全A股票市场中位数
# Avg(0.25Q ~ 0.75Q)
def Calculate_Aggregate_ROE(database, datetime1, datetime2, save_to_database=False):
    #
    filter = []
    filter.append(["report_date", ">=", datetime1])
    filter.append(["report_date", "<=", datetime2])
    documents = database.Find("Factor", "ROE_TTM", filter=filter)
    df = Gadget.DocumentsToDataFrame(documents)
    droupbyDate = df.groupby("report_date")
    #
    data = []
    for group in droupbyDate:
        #
        reportDate = group[0]
        period = Gadget.report_date_to_period(reportDate)
        #
        if period == 1:
            releaseDate = datetime.datetime(reportDate.year, 5, 1)
        elif period == 4:
            releaseDate = datetime.datetime(reportDate.year+1, 5, 1)
        elif period == 2:
            releaseDate = datetime.datetime(reportDate.year, 9, 1)
        elif period == 3:
            releaseDate = datetime.datetime(reportDate.year, 11, 1)

        df = group[1]
        median = df["value"].median()
        #
        q25 = df["value"].quantile(0.05)
        q75 = df["value"].quantile(0.95)
        dfFiltered = df[(df["value"] >= q25) & (df["value"] <= q75)]
        average = dfFiltered['value'].mean()
        #
        print(reportDate, releaseDate, median, average) # 注意这个不是PPT的原版代码 roe med， roe avg
        data.append([reportDate, releaseDate, median, average])
    #
    dfFactor = pd.DataFrame(data, columns=["ReportDate", "ReleaseDate", "Median", "Average"])
    print(dfFactor)

    #
    if save_to_database:
        Save_Systematic_Factor_To_Database(database, dfFactor, save_name='ROE_Median_TotalA', field_name="Median")
        Save_Systematic_Factor_To_Database(database, dfFactor, save_name='ROE_Average_TotalA', field_name="Average")


#
def get_index_hist_content(database, datetime1, datetime2, index_symbol=""):
    #
    filter = [("index_symbol", index_symbol), ("date",">=",datetime1), ("date","<=",datetime2)]
    df = database.GetDataFrame("financial_data", "index_constituents", filter=filter)
    df.drop_duplicates("symbol", inplace=True)
    #
    return df


# 整体法
def calc_roe_total_method(database, datetime1, datetime2, market="jp"):
    #
    report_date_list = Gadget.generate_report_date_list(datetime1, datetime2)
    #
    df_stock_list = pd.DataFrame()
    # df_stock_list = get_index_hist_content(database, datetime1, datetime2, index_symbol="000300.SH")
    # df_stock_list = load_historical_nikkei225_content(remove_duplicate=True)

    #
    list_df = []
    # for year in range(2008, 2023):
    for report_date in report_date_list:
        # print(year)
        # report_date = datetime.datetime(year, 12, 31)
        date_filter = {"report_date": report_date}
        df_fundamental = database.GetDataFrame("financial_data", "stock_fundamental_basic", filter=date_filter)

        #
        if df_fundamental.empty:
            continue

        # 分子
        # ttm_field = "np_belongto_parcomsh"
        # ttm_field = "net_income_avl_cs"
        # df_ttm = calc_total_fundamental_value_ttm(database, report_date, ttm_field=ttm_field, market=market)
        #
        # #
        # if df_ttm.empty:
        #     continue
        #
        # # 分母
        # avg_field = "tot_equity"
        # df_avg = calc_total_fundamental_value_avg(database, report_date, avg_field=avg_field, market=market)
        #
        # # 计算ROE
        # df_avg = df_avg[df_avg["avg"] > 0].copy()  # 去除异常值
        # df = pd.merge(df_ttm, df_avg, how="inner", on="symbol")
        #
        # #
        df = df_fundamental
        if not df_stock_list.empty:
            df = pd.merge(df_stock_list[["symbol"]], df, on="symbol", how="inner")
        # #
        # total_equity = df["avg"].sum()
        # total_earning = df["ttm"].sum()
        # roe = total_earning / total_equity

        total_equity = df["tot_equity"].sum()
        total_sales = df["tot_oper_rev"].sum()
        total_assets = df["tot_assets"].sum()
        total_earning = df["np_belongto_parcomsh"].sum()
        #
        roe = total_earning / total_equity
        margin = total_earning / total_sales
        turnover = total_sales / total_assets
        leverage = total_assets / total_equity
        #
        print(report_date, len(df), total_earning, total_equity, roe, margin, turnover, leverage)
        aa = 0
        #
        # df_keep = df[["symbol", "description", "report_date", "period", "roe", "tot_equity", "net_income_avl_cs"]].copy()
        # list_df.append(df_keep)
        #
        # if not df_last.empty:
        #     df_last.rename(columns={"roe": "roe_lag_1"}, inplace=True)
        #     df = pd.merge(df_last[["symbol","roe_lag_1"]], df[["symbol", "roe", "description"]], how="inner", on="symbol")
        # df_last = df
    #
    # df = pd.concat(list_df, axis=0)


# 整体法, 简单看年末
def calc_roe_total_method_2(database, datetime1, datetime2, market="jp"):
    #
    report_date_list = Gadget.generate_report_date_list(datetime1, datetime2)
    #
    df_stock_list = pd.DataFrame()
    # df_stock_list = get_index_hist_content(database, datetime1, datetime2, index_symbol="000300.SH")
    df_stock_list = load_historical_nikkei225_content(remove_duplicate=True)

    #
    list_df = []
    for year in range(datetime1.year, datetime2.year+1):
        # print(year)
        report_date = datetime.datetime(year, 12, 31)
        date_filter = {"report_date": report_date}
        #
        if market.lower() == "jp":
            df_fundamental = database.GetDataFrame("financial_data", "stock_jp_fundamental_gsd", filter=date_filter)
        else:
            df_fundamental = database.GetDataFrame("financial_data", "stock_fundamental_basic", filter=date_filter)

        #
        if df_fundamental.empty:
            continue

        #
        if not df_stock_list.empty:
            df = pd.merge(df_stock_list[["symbol"]], df_fundamental, on="symbol", how="inner")
        else:
            df = df_fundamental

        #
        if market.lower() == "jp":
            df["roe"] = df["net_income_avl_cs"] / df["tot_equity"]
            df["margin"] = df["net_income_avl_cs"] / df["sales"]
            df["turnover"] = df["sales"] / df["tot_assets"]
            df["leverage"] = df["tot_assets"] / df["tot_equity"]
            #
            total_equity = df["tot_equity"].sum()
            total_sales = df["sales"].sum()
            total_assets = df["tot_assets"].sum()
            total_earning = df["net_income_avl_cs"].sum()
        else:
            df["roe"] = df["np_belongto_parcomsh"] / df["tot_equity"]
            df["margin"] = df["np_belongto_parcomsh"] / df["tot_oper_rev"]
            df["turnover"] = df["tot_oper_rev"] / df["tot_assets"]
            df["leverage"] = df["tot_assets"] / df["tot_equity"]
            #
            total_equity = df["tot_equity"].sum()
            total_sales = df["tot_oper_rev"].sum()
            total_assets = df["tot_assets"].sum()
            total_earning = df["np_belongto_parcomsh"].sum()
        #
        roe = total_earning / total_equity
        margin = total_earning / total_sales
        turnover = total_sales / total_assets
        leverage = total_assets / total_equity
        #
        print(report_date, len(df), total_earning, total_equity, roe, margin, turnover, leverage)
        #
        path = r"C:\Users\kkwoo\Documents\日本股市研究\\"
        df_keep = df[["symbol", "report_date", "period", "roe", "margin", "turnover", "leverage"]].copy()
        #
        if market=="jp":
            df_keep.to_excel(path + "日经ROE分解_" + Gadget.ToDateString2(report_date) + ".xlsx")
        else:
            df_keep.to_excel(path + "A股ROE分解_" + Gadget.ToDateString2(report_date) + ".xlsx")
        aa = 0
    #
    # df = pd.concat(list_df, axis=0)


if __name__ == '__main__':
    #
    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    datetime1 = datetime.datetime(2005, 12, 1)
    datetime2 = datetime.datetime(2025, 12, 31)

    # df = get_index_hist_content(database, datetime1, datetime2, index_symbol="000300.SH")

    # 整体法求ROE
    # calc_roe_total_method(database, datetime1, datetime2)
    #
    # datetime1 = datetime.datetime(2021, 12, 1)
    # datetime2 = datetime.datetime(2021, 12, 31)
    # calc_roe_total_method_2(database, datetime1, datetime2)
    #
    Calculate_Aggregate_ROE(database, datetime1, datetime2, save_to_database=False)